#!/usr/local/bin/perl5
# $Id$

use strict qw(vars);
use Data::Dumper;
use File::Basename;
use FixInc::Logger;
use FixInc::Halo::API::Audit;
use FixInc::OopOptsDB;
use Feeds::API::File;
use POSIX qw(strftime);
use Readonly;
use warnings ( FATAL => 'all' );
use Sybase::CTlib;

Readonly my $SCRIPT_NM      => basename($0);





################################
# Script Variables
#
#
################################
my $log;
my $oa_dbh;
my $party_dbh;
my $options;




exit main();

sub main 
{
    _init();
    
    my $ret = _invoke_plugin();
    
    if ($ret == 0)
    {
        return 0;	
    }
    else
    {
        return -1;	
    }   
}




#------Private functions ----------------
sub _init 
{
    $log    = FixInc::Logger->new( min_level => 'notice' );
    
    my ( $prog, $prog_args );
    $log->notice("=== $SCRIPT_NM has started ===");

    # parse the parameters from the command line    
    ( $prog_args, $options, $prog ) = _parse_cmd_line();
    
    $log->paramApply($options);
}



sub _invoke_plugin 
{
    my %classArgs;
    $classArgs{log}       = $log         if defined $log;
    $classArgs{options}   = $options     if defined $options;
    
    my $self = 
    {
        log                 => $classArgs{log},
        options             => $classArgs{options},
    };
    
    my $log = $self->{log};
    $log->notice(__PACKAGE__, "Calling new() in Transform module...");
    
    my $options = $self->{options};

    bless $self;
    
    # party_dbh
    #
    $self->{party_dbh} = FixInc::Halo::API::Audit->connect(
        'server' => $options->{party_server},
        'username' => $options->{party_username},
        'password' => $options->{party_password},
        'charset' => 'utf8',
        'halodb' => $options->{halo_database},
        'halouser' => $options->{halo_username},
        'haloappl' => $options->{halo_applname},
        'log' => $log,
    )
    or $log->die($FixInc::Halo::API::Audit::errstr);

    if ( defined($options->{party_database}) )
    {
        $self->{party_dbh}->do("use $options->{party_database}")
            or $log->die( $self->{party_dbh}->errstr );
    }

    # oa_dbh
    #
    $self->{oa_dbh} = FixInc::DBAccess->connect(
        server => $options->{oa_server},
        username => $options->{oa_username},
        password => $options->{oa_password},
        charset => 'utf8',
        log => $log
    )
    or $log->die($FixInc::DBAccess::errstr);

    if ( defined($options->{oa_database}) )
    {
        $self->{oa_dbh}->do("use $options->{oa_database}")
            or $log->die( $self->{oa_dbh}->errstr );
    }
    
    my $party_dbh        = $self->{party_dbh};
    my $oa_dbh           = $self->{oa_dbh};
    
#    $log->info("111111111111111111111111111111111111 procedure ST");
#    
#    my $sql = "
#            BEGIN
#                EXEC fi_update_party
#            END";
#    $oa_dbh->do($sql) or $log->die($oa_dbh->errstr);
#    
#    $log->info("111111111111111111111111111111111111 procedure ED");
    
    my $up_sql;
    my $party_sql;
    my $party_rows;
    my $party_id = undef;
    my $result_party_id = undef;
    my $immed_success = 0;
    my $ultimate_success = 0;
    
    $log->info("111111111111111111111111111111111111 cycle ST");
    
    my $immed_sql = "
select p.oa_parent_orgid as oa_parent_orgid,p.party_id as party_id
from  oa_fi_int..oa_rc_party p,oa_fi_int..entity_party_map e
where p.cur_immed_parent_id is null
  and p.oa_parent_orgid is not null
  and e.party_id = p.party_id
  and e.obj_type_cd = 'GCPA' ";
  
    $log->debug($immed_sql);
    my $immed_rows = $oa_dbh->selectall_hashes($immed_sql) or $log->die($oa_dbh->errstr);
    foreach my $immed_row (@$immed_rows)
    {
        $party_id           = $immed_row->{party_id};
        my $oa_parent_orgid = $immed_row->{oa_parent_orgid};
        $party_sql = "select top 1 party_id from dmo_govcorp..party where issuer_orgid = '$oa_parent_orgid'";
        
        $log->debug($party_sql);
        $party_rows = $party_dbh->selectall_hashes($party_sql) or $log->die($party_dbh->errstr);
        $result_party_id = defined ($party_rows->[0]->{party_id})?$party_rows->[0]->{party_id}:'';
        
        if (defined $result_party_id and $result_party_id) {
            $log->info("cur_immed_parent_id request party_id = $party_id, response party_id = $result_party_id");
        } else {
            $log->info("cur_immed_parent_id request party_id $party_id, response party_id =");
        }
        
        if (defined $result_party_id and $result_party_id) {
            $immed_success = $immed_success + 1;
            $up_sql = "update oa_fi_int..oa_rc_party set cur_immed_parent_id = '$result_party_id' where party_id = '$party_id'";
            $log->debug($up_sql);
            $oa_dbh->do($up_sql) or $log->die($oa_dbh->errstr);
        }
    }
  
    my $ultimate_sql = "
select p.oa_root_orgid as oa_root_orgid,p.party_id as party_id
from  oa_fi_int..oa_rc_party p,oa_fi_int..entity_party_map e
where p.cur_ultimate_parent_id is null
  and p.oa_root_orgid is not null
  and e.party_id = p.party_id
  and e.obj_type_cd = 'GCPA' ";
  
    $log->debug($ultimate_sql);
    my $ultimate_rows = $oa_dbh->selectall_hashes($ultimate_sql) or $log->die($oa_dbh->errstr);
    foreach my $ultimate_row (@$ultimate_rows)
    {
        $party_id                  = $ultimate_row->{party_id};
        my $oa_root_orgid          = $ultimate_row->{oa_root_orgid};
        $party_sql = "select top 1 party_id from dmo_govcorp..party where issuer_orgid = '$oa_root_orgid'";
        
        $log->debug($party_sql);
        $party_rows = $party_dbh->selectall_hashes($party_sql) or $log->die($party_dbh->errstr);
        $result_party_id = $party_rows->[0]->{party_id};
        
        if (defined $result_party_id and $result_party_id) {
            $log->info("cur_ultimate_parent_id request party_id = $party_id, response party_id = $result_party_id");
        } else {
            $log->info("cur_ultimate_parent_id request party_id $party_id, response party_id =");
        }
        
        if (defined $result_party_id and $result_party_id) {
            $ultimate_success = $ultimate_success + 1;
            $up_sql = "update oa_fi_int..oa_rc_party set cur_ultimate_parent_id = '$result_party_id' where party_id = '$party_id'";
            $log->debug($up_sql);
            $oa_dbh->do($up_sql) or $log->die($oa_dbh->errstr);
        }
    }
    
    my $immed_rows_count = scalar @$immed_rows;
    my $ultimate_rows_count = scalar @$ultimate_rows;
    $log->info("immed_rows_count    = $immed_rows_count,   immed_success count = $immed_success");
    $log->info("ultimate_rows_count = $ultimate_rows_count,ultimate_success    = $ultimate_success");
    $log->info("111111111111111111111111111111111111 cycle ED");
    
#    my $drop_sql = "
#BEGIN
#    drop proc fi_update_party
#END";
#    $oa_dbh->do($drop_sql) or $log->die($oa_dbh->errstr);
}



###################################################################
#   
# Description : parse the command line options
#
# Command line options : 
#
#######################################################################
sub _parse_cmd_line 
{
    my $defs = FixInc::OopOptsDB->newOptSet(
        help => <<'EOH',
	This program bucket some bonds in wm solvability vendor DB.
    For more help: oa_gc_xform.pl -hh
EOH

        defaults => {
         }, 

        optdefs => FixInc::OopOptsDB->newOptDef( 
            {   name     => 'party_server',
                aliases  => ['ps'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Party Database Server'
            },
            {   name     => 'party_database',
                aliases  => ['pd'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Party Database Name'
            },
            {   name     => 'party_username',
                aliases  => ['pu'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Party Database User Name'
            },
            {   name     => 'party_password',
                aliases  => ['pp'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Party Database User Password'
            },
            {   name     => 'dmo_database',
                aliases  => ['dd'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'dmo Database Name'
            },
            {   name     => 'oa_server',
                aliases  => ['os'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'OA Database Server'
            },
            {   name     => 'oa_database',
                aliases  => ['od'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'OA Database Name'
            },
            {   name     => 'oa_username',
                aliases  => ['ou'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'OA Database User Name'
            },
            {   name     => 'oa_password',
                aliases  => ['op'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'OA Database User Password'
            },
            {   name     => 'dbor',
                aliases  => ['DBOR'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                'adjust'  => sub 
                             {
                             defined $_[1] ? $_[1] =~ /^(GCPA|MUPA)$/imgx ? uc $_[1] : undef: $_[1];
                             },
                help     => 'DBOR:GCPA/MUPA'
            },
            {   name    => 'ident_list',
                aliases => ['IL'],
                maxargs => 30,
                required => 0,
                type    => 'file',
                help    => 'List of identifiers to xform.'
            },
            {   name     => 'plugin',
                aliases  => ['PL'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'The plugin that will control the mapping.'
            },
            {   name     => 'max_asset_tran_cnt',
                aliases  => ['TC'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Max Asset Transaction Count.'
            },
            {   name     => 'score_switch',
                aliases  => ['SS'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Score Switch.'
            },
            {   name     => 'filter_switch',
                aliases  => ['FS'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'update Filter Switch. ignore update if oa_rc_xxx data are not changed'
            },
            {   name    => 'run_mode',
                aliases => ['RM'],
                maxargs => 1,
                type    => 'string',
                'adjust'  => sub 
                             {
                             defined $_[1] ? $_[1] =~ /^(I|F)$/imgx ? uc $_[1] : undef: $_[1];
                             },
                help    => 'run mode control.'
            }, 
            {   name     => 'short_name_threhold',
                aliases  => ['SNT'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Max length of party short name.'
            }, 
            {   name     => 'common_name_threhold',
                aliases  => ['CNT'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Max length of party comment(long) name.'
            },
            {   name    => 'name_hist_action',
                aliases => ['NHA'],
                maxargs => 1,
                type    => 'string',
                'adjust'  => sub 
                             {
                             defined $_[1] ? $_[1] =~ /^(I|U)$/imgx ? uc $_[1] : undef: $_[1];
                             },
                help    => 'party_name_hist actions. I : insert, U: update'
            },  
            {   name    => 'name_hist_ticker',
                aliases => ['TIC'],
                maxargs => 1,
                type    => 'string',
                help    => 'party_name_hist hist_party_ticker'
            }, 
            {   name    => 'name_hist_rtr_ticker',
                aliases => ['RTIC'],
                maxargs => 1,
                type    => 'string',
                help    => 'party_name_hist hist_party_rtr_ticker'
            }, 
            {   name     => 'undwrt_cd',
                aliases  => ['UW'],
                maxargs  => 1,
                type     => 'string',
                help     => 'party_name_hist undwrt_cd'
            },
            {   name    => 'approved_exc_cds',
                aliases => ['AE'],
                maxargs => 20,
                required => 0,
                type    => 'file',
                help    => 'List of approved exception cd list, used in F Mode(Rebucket/Force Load/Bulk Load).'
            },
            {   name     => 'max_refreshed_ent_cnt',
                aliases  => ['MRE'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Max Refreshed Entity Count.'
            },
            {   name     => 'max_proc_extraction_dt_cnt',
                aliases  => ['MPED'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Max processed fi_extraction table rows Count.'
            },
            {   name     => 'max_entity_party_map_del_cnt',
                aliases  => ['MEPM'],
                maxargs  => 1,
                required => 1,
                type     => 'string',
                help     => 'Max processed entity_party_map_del table rows Count.'
            },
            {   name     => 'debug',
                aliases  => ['g'],
                maxargs  => 0,
                required => 0,
                type     => 'string',
                help     => 'Generate debug information, including SQL queries, if available.'
            },
        )
    );

    my $log = FixInc::Logger->new( min_level => 'info' );

    my $oops = FixInc::OopOptsDB->new(
        'optset' => $defs,
        'log'    => $log
        )
        or $log->die("Cannot process options: FixInc::OopOptsDB->new");
    return ( $defs->name, $oops->opthash, $oops->{$SCRIPT_NM} );
}

# Returns: n/a
#
END {

    $log->notice("=== $SCRIPT_NM has stopped ===");
}    # END

__END__









